package com.example.demo.web.dao;

import java.util.Date;
import java.util.List;

import org.apache.logging.log4j.LogManager;
import org.apache.logging.log4j.Logger;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.dao.DuplicateKeyException;
import org.springframework.dao.EmptyResultDataAccessException;
import org.springframework.jdbc.core.BeanPropertyRowMapper;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.stereotype.Repository;

import com.example.demo.web.Entity.KeyEntity;
import com.example.demo.web.Entity.LockEntity;
import com.example.demo.web.Entity.LockOpenLogEntity;
import com.example.demo.web.Entity.LockUserEntity;
import com.example.demo.web.Entity.OrganizationEntity;
import com.example.demo.web.util.XinLongUtil;

@Repository
public class LockDAO {
	private Logger log = LogManager.getLogger(LockDAO.class);
	private JdbcTemplate jdbcTemplate;

    @Autowired
	public void setJdbcTemplate(JdbcTemplate jdbcTemplate) {
		this.jdbcTemplate = jdbcTemplate;
	}

    public List<LockEntity> findAll(int pagenum,int pagesize,String orgname,String opername,String locksn,List<OrganizationEntity> orgs){
    	StringBuilder sb = new StringBuilder();
    	int i = 0;
    	for(OrganizationEntity org : orgs){
    		if(i == 0){
    			sb.append(org.getId() + "'");
    		}else{
    			sb.append(" or tlock.orgid = '" + org.getId()+ "'");
    		}
    		i++;
    	}
    	String sql = "SELECT tlock.*, org.Name as orgname, lockuser.operators as operators, lockuser.oper_reals as oper_reals,lockuser.active as authed FROM tbl_lock tlock left join tbl_organization org on org.id = tlock.orgid left join tbl_lock_user lockuser on lockuser.locksn = tlock.locksn where tlock.active = 0 and (tlock.orgid = '" + sb.toString() + ")";
    	
    	if(!locksn.equals("")) {
    		sql += " and tlock.locksn like '%" + locksn + "'"; 
    	}
    	
    	if(!opername.equals("")) {
    		sql += " and lockuser.oper_reals like '%" + opername + "%'";
    	}
    	
    	if(!orgname.equals("")) {
    		sql += " and org.Name like '%" + orgname + "%'"; 
    	}
    	
    	sql += " order by tlock.locksn limit " + (pagenum-1)*pagesize + "," + pagesize;   
    	return jdbcTemplate.query(sql, BeanPropertyRowMapper.newInstance(LockEntity.class));
    }
    
    public List<LockEntity> findAll(int pagenum,int pagesize,String orgname,String opername,String locksn){
    	
    	String sql = "SELECT tlock.*, org.Name as orgname, lockuser.operators as operators, lockuser.oper_reals as oper_reals,lockuser.active as authed FROM tbl_lock tlock left join tbl_organization org on org.id = tlock.orgid left join tbl_lock_user lockuser on lockuser.locksn = tlock.locksn where tlock.active = 0";
    	
    	if(!locksn.equals("")) {
    		sql += " and tlock.locksn like '%" + locksn + "'"; 
    	}
    	
    	if(!opername.equals("")) {
    		sql += " and lockuser.oper_reals like '%" + opername + "%'";
    	}
    	
    	if(!orgname.equals("")) {
    		sql += " and org.Name like '%" + orgname + "%'"; 
    	}
    	
    	sql += " order by tlock.locksn limit " + (pagenum-1)*pagesize + "," + pagesize;   
    	return jdbcTemplate.query(sql, BeanPropertyRowMapper.newInstance(LockEntity.class));
    }
    
    public List<LockEntity> findAll(){
    	String sql = "SELECT * FROM tbl_lock";       
    	return jdbcTemplate.query(sql, BeanPropertyRowMapper.newInstance(LockEntity.class));
    }
    
    public int getCount(String orgname,String opername,String locksn,List<OrganizationEntity> orgs){
    	StringBuilder sb = new StringBuilder();
    	int i = 0;
    	for(OrganizationEntity org : orgs){
    		if(i == 0){
    			sb.append(org.getId() + "'");
    		}else{
    			sb.append(" or tlock.orgid = '" + org.getId()+ "'");
    		}
    		i++;
    	}
    	String sql = "SELECT count(*) FROM tbl_lock tlock left join tbl_organization org on org.id = tlock.orgid left join tbl_lock_user lockuser on lockuser.locksn = tlock.locksn where tlock.active = 0 and (tlock.orgid = '" + sb.toString() + ")";
    	
    	if(!locksn.equals("")) {
    		sql += " and tlock.locksn like '%" + locksn + "'"; 
    	}
    	if(!opername.equals("")) {
    		sql += " and lockuser.oper_reals like '%" + opername + "%'";
    	}
    	if(!orgname.equals("")) {
    		sql += " and org.Name like '%" + orgname + "%'"; 
    	}
    	return jdbcTemplate.queryForObject(sql, Integer.class);
    }
    
    public int getCount(String orgname,String opername,String locksn){
    	String sql = "SELECT count(*) FROM tbl_lock tlock left join tbl_organization org on org.id = tlock.orgid left join tbl_lock_user lockuser on lockuser.locksn = tlock.locksn where tlock.active = 0";
    	
    	if(!locksn.equals("")) {
    		sql += " and tlock.locksn like '%" + locksn + "'"; 
    	}
    	if(!opername.equals("")) {
    		sql += " and lockuser.oper_reals like '%" + opername + "%'";
    	}
    	if(!orgname.equals("")) {
    		sql += " and org.Name like '%" + orgname + "%'"; 
    	}
    	return jdbcTemplate.queryForObject(sql, Integer.class);
    }
    
    public List<LockEntity> findByUser(int pagenum,int pagesize,String locksn,String username){
    	String sql = "SELECT tlock.*, org.Name as orgname, lockuser.operators as operators, lockuser.oper_reals as oper_reals,lockuser.active as authed FROM tbl_lock tlock left join tbl_lock_user lockuser on lockuser.locksn = tlock.locksn left join tbl_organization org on org.id = tlock.orgid where tlock.active = 0 and lockuser.operators like '%" + username + "%'";

    	if(!locksn.equals("")) {
    		sql += " and tlock.locksn like '%" + locksn + "'"; 
    	}
    	sql += " order by tlock.locksn limit " + (pagenum-1)*pagesize + "," + pagesize;   
    	return jdbcTemplate.query(sql, BeanPropertyRowMapper.newInstance(LockEntity.class));
    }
    
    public int getfindByUserCount(String locksn,String username){
    	String sql = "SELECT count(*) FROM tbl_lock tlock left join tbl_lock_user lockuser on lockuser.locksn = tlock.locksn where tlock.active = 0 and lockuser.operators like '%" + username + "%'";
    	if(!locksn.equals("")) {
    		sql += " and tlock.locksn like '%" + locksn + "'"; 
    	}
    	return jdbcTemplate.queryForObject(sql, Integer.class);
    }
    
    public LockEntity findBySn(String locksn){
    	String sql = "SELECT * FROM tbl_lock WHERE locksn='" + locksn + "'";
        try{
        	return  jdbcTemplate.queryForObject(sql, BeanPropertyRowMapper.newInstance(LockEntity.class));
        }catch(Exception e){
        	XinLongUtil.xinLongUtil.printlog(log,e.getMessage());
        	return null;
        }
    	
    }  
    
    public int updatelock(LockEntity lockEntity){

		// update
		String sql = "UPDATE tbl_lock SET "
					+ "area=?, regtime=?, offtime=?, active=?,opentype=?,locktype=?, cname=?, orgid=?,boxid=?,descriptor=? WHERE locksn=?";
		try{
			jdbcTemplate.update(sql, lockEntity.getArea(),
					lockEntity.getRegtime(), lockEntity.getOfftime(),lockEntity.getActive(),lockEntity.getOpentype(),lockEntity.getLocktype(),lockEntity.getCname(),lockEntity.getOrgid(),lockEntity.getBoxid(),lockEntity.getDescriptor()
					,lockEntity.getLocksn());
		}catch(Exception e){
			XinLongUtil.xinLongUtil.printlog(log,e.getMessage());
			return 0;
		}
		return 1;
    	
    }
    
    public int savelock(LockEntity lockEntity){    	
		// insert
		String sql = "INSERT INTO tbl_lock (id,locksn, area,regtime,offuser,offtime,active,opentype,locktype,cname,electricity,missionid,authed,orgid,boxid,descriptor)"
                + " VALUES (?, ?, ?, ?,?,?,?,?,?,?,?,?,?,?,?,?)";
		try{
			jdbcTemplate.update(sql, lockEntity.getId(),lockEntity.getLocksn(),
					lockEntity.getArea(), lockEntity.getRegtime(),lockEntity.getOffuser(), lockEntity.getOfftime(),lockEntity.getActive(),lockEntity.getOpentype(),lockEntity.getLocktype(),lockEntity.getCname(),
					lockEntity.getElectricity(),lockEntity.getMissionid(),lockEntity.getAuthed(),lockEntity.getOrgid(),lockEntity.getBoxid(),lockEntity.getDescriptor());
		}catch(DuplicateKeyException dx){
			XinLongUtil.xinLongUtil.printlog(log,dx.getMessage());
			return 2;
		}catch(Exception e){
			XinLongUtil.xinLongUtil.printlog(log,e.getMessage());
			return 0;
		}
    	return 1;
    	
    	
    }
    
    public int delete(String locksn) {
        String sql = "DELETE FROM tbl_lock WHERE locksn=?";
        try{
        	return jdbcTemplate.update(sql, locksn);
        }catch(Exception e){
        	XinLongUtil.xinLongUtil.printlog(log,e.getMessage());
        	return 0;
        }
        
    }
    
    public int offlock(String locksn,String offdate,String offuser){

		// update
		String sql = "UPDATE tbl_lock SET active=?,offtime=?,offuser=? WHERE locksn=?";
		try{
			jdbcTemplate.update(sql, 1,offdate,offuser, locksn);
		}catch(Exception e){
			XinLongUtil.xinLongUtil.printlog(log,e.getMessage());
			return 0;
		}
		return 1;
    	
    }
    
    public int setAuthByMission(int active,String missionid){

		// update
		String sql = "UPDATE tbl_lock SET active=? WHERE missionid=?";
		try{
			jdbcTemplate.update(sql, active, missionid);
		}catch(Exception e){
			XinLongUtil.xinLongUtil.printlog(log,e.getMessage());
			return 0;
		}
		return 1;
    	
    }
    
    public List<LockEntity> findOffAll(int pagenum,int pagesize,String orgname,String locksn,List<OrganizationEntity> orgs){
    	StringBuilder sb = new StringBuilder();
    	int i = 0;
    	for(OrganizationEntity org : orgs){
    		if(i == 0){
    			sb.append(org.getId() + "'");
    		}else{
    			sb.append(" or tlock.orgid = '" + org.getId()+ "'");
    		}
    		i++;
    	}
    	String sql = "SELECT tlock.*, org.Name as orgname FROM tbl_lock tlock left join tbl_organization org on org.id = tlock.orgid where active = 1 and (tlock.orgid = '" + sb.toString() + ")";
    	if(!orgname.equals("")) {
    		sql += " and org.Name LIKE '%" + orgname + "%'"; 
    	}
    	if(!locksn.equals("")) {
    		sql += " and tlock.locksn like '%" + locksn + "'"; 
    	}
    	sql += " order by org.Orgcode limit " + (pagenum-1)*pagesize + "," + pagesize;   
    	return jdbcTemplate.query(sql, BeanPropertyRowMapper.newInstance(LockEntity.class));
    }
    
    public int getOffCount(String orgname,String locksn,List<OrganizationEntity> orgs){
    	StringBuilder sb = new StringBuilder();
    	int i = 0;
    	for(OrganizationEntity org : orgs){
    		if(i == 0){
    			sb.append(org.getId() + "'");
    		}else{
    			sb.append(" or tlock.orgid = '" + org.getId()+ "'");
    		}
    		i++;
    	}
    	String sql = "SELECT count(*) FROM tbl_lock tlock left join tbl_organization org on org.id = tlock.orgid where active = 1 and (tlock.orgid = '" + sb.toString() + ")";
    	if(!orgname.equals("")) {
    		sql += " and org.Name LIKE '%" + orgname + "%'"; 
    	}
    	if(!locksn.equals("")) {
    		sql += " and tlock.locksn like '%" + locksn + "'"; 
    	}
    	return jdbcTemplate.queryForObject(sql, Integer.class);
    }
    
    public List<LockEntity> findLocksBySearch(String owner,String locksn,String orgid){
    	String sql = "select tlock.*,org.Name as orgname,lockuser.operators as operators,lockuser.oper_reals as oper_reals from tbl_lock tlock left join tbl_organization org on org.id = tlock.orgid left join tbl_lock_user lockuser on lockuser.locksn = tlock.locksn where tlock.opentype = 0 and tlock.active = 0 and tlock.orgid = '" + orgid + "'";
    	
    	if(!owner.equals("")) {
    		sql += " and lockuser.oper_reals = '" + owner + "'"; 
    	}
    	if(!locksn.equals("")) {
    		sql += " and tlock.locksn like '%" + locksn + "'"; 
    	}
    	sql += " order by tlock.locksn";   
    	return jdbcTemplate.query(sql, BeanPropertyRowMapper.newInstance(LockEntity.class));
    }
    
    public List<LockEntity> findAllotLocksBySearch(String boxid,String locksn,String orgid){
    	String sql = "select tlock.*,org.Name as orgname,lockuser.operators as operators,lockuser.oper_reals as oper_reals from tbl_lock tlock left join tbl_organization org on org.id = tlock.orgid left join tbl_lock_user lockuser on lockuser.locksn = tlock.locksn where tlock.opentype = 0 and tlock.locktype = 3 and tlock.active = 0 and tlock.orgid = '" + orgid + "'";
    	
    	if(!boxid.equals("")) {
    		sql += " and tlock.boxid = '" + boxid + "'"; 
    	}
    	if(!locksn.equals("")) {
    		sql += " and tlock.locksn like '%" + locksn + "'"; 
    	}
    	sql += " order by tlock.locksn";   
    	return jdbcTemplate.query(sql, BeanPropertyRowMapper.newInstance(LockEntity.class));
    }
    
    public List<LockEntity> findDoubleLocksBySearch(String owner,String locksn,String orgid){
    	String sql = "select tlock.*,org.Name as orgname,lockuser.operators as operators,lockuser.oper_reals as oper_reals from tbl_lock tlock left join tbl_organization org on org.id = tlock.orgid left join tbl_lock_user lockuser on lockuser.locksn = tlock.locksn where tlock.opentype = 1 and tlock.active = 0 and tlock.orgid = '" + orgid + "'";
    	
    	if(!owner.equals("")) {
    		sql += " and lockuser.oper_reals like '%" + owner + "%'"; 
    	}
    	if(!locksn.equals("")) {
    		sql += " and tlock.locksn like '%" + locksn + "'"; 
    	}
    	sql += " order by tlock.locksn";   
    	return jdbcTemplate.query(sql, BeanPropertyRowMapper.newInstance(LockEntity.class));
    }
    
    /*====================================================================tbl_lockopenlog===============================================================================*/
    public int insertopenLog(LockOpenLogEntity logEntity){    	
    	String sql = "INSERT INTO tbl_lockopenlog (id,keysn,locksn,opentime, operator,proceed)"
                + " VALUES (?, ?, ?, ?,?,?)";
    	try{
    		return jdbcTemplate.update(sql,logEntity.getId(),logEntity.getKeysn(),logEntity.getLocksn(),logEntity.getOpentime(), logEntity.getOperator(),logEntity.getProceed());
    	}catch(DuplicateKeyException dx){
			XinLongUtil.xinLongUtil.printlog(log,dx.getMessage());
			return 0;
		}catch(Exception e){
    		XinLongUtil.xinLongUtil.printlog(log,e.getMessage());
    		return -1;
    	}    	
    }
    
    public List<LockOpenLogEntity> findOpenLogBySearch(int pagenum,int pagesize,String owner,String locksn,String keysn,String orgname){
    	String sql = "select olog.*,org.Name as orgname,us.RealName as oper_reals from tbl_lockopenlog olog left join tbl_users us on us.Username = olog.operator left join tbl_organization org on org.id = us.org_id where us.Username != 'hzwlprevail'";

    	if(!owner.equals("")) {
    		sql += " and us.RealName like '%" + owner + "%'"; 
    	}
    	if(!locksn.equals("")) {
    		sql += " and olog.locksn like '%" + locksn + "'"; 
    	}
    	if(!keysn.equals("")) {
    		sql += " and olog.keysn like '%" + keysn + "'"; 
    	}
    	if(!orgname.equals("")) {
    		sql += " and org.Name like '%" + orgname + "%'"; 
    	}
    	sql += " order by org.Orgcode,olog.locksn limit " + (pagenum-1)*pagesize + "," + pagesize;   
    	return jdbcTemplate.query(sql, BeanPropertyRowMapper.newInstance(LockOpenLogEntity.class));
    }
    
    public int findOpenLogCountBySearch(String owner,String locksn,String keysn,String orgname){
    	String sql = "select count(*) from tbl_lockopenlog olog left join tbl_users us on us.Username = olog.operator left join tbl_organization org on org.id = us.org_id where us.Username != 'hzwlprevail'";
    	
    	if(!owner.equals("")) {
    		sql += " and us.RealName like '%" + owner + "%'"; 
    	}
    	if(!locksn.equals("")) {
    		sql += " and olog.locksn like '%" + locksn + "'"; 
    	}
    	if(!keysn.equals("")) {
    		sql += " and olog.keysn like '%" + keysn + "'"; 
    	}
    	if(!orgname.equals("")) {
    		sql += " and org.Name like '%" + orgname + "%'"; 
    	} 
    	return jdbcTemplate.queryForObject(sql, Integer.class);
    }
    
    public List<LockOpenLogEntity> findOpenLogBySearch(int pagenum,int pagesize,String owner,String locksn,String keysn,String orgname,List<OrganizationEntity> orgs){
    	StringBuilder sb = new StringBuilder();
    	int i = 0;
    	for(OrganizationEntity org : orgs){
    		if(i == 0){
    			sb.append(org.getId() + "'");
    		}else{
    			sb.append(" or us.org_id = '" + org.getId()+ "'");
    		}
    		i++;
    	}
    	String sql = "select olog.*,org.Name as orgname,us.RealName as oper_reals from tbl_lockopenlog olog left join tbl_users us on us.Username = olog.operator left join tbl_organization org on org.id = us.org_id where (us.org_id = '" + sb.toString();

    	if(!owner.equals("")) {
    		sql += " and us.RealName like '%" + owner + "%'"; 
    	}
    	if(!locksn.equals("")) {
    		sql += " and olog.locksn like '%" + locksn + "'"; 
    	}
    	if(!keysn.equals("")) {
    		sql += " and olog.keysn like '%" + keysn + "'"; 
    	}
    	if(!orgname.equals("")) {
    		sql += " and org.Name like '%" + orgname + "%'"; 
    	}
    	sql += " order by org.Orgcode,olog.locksn limit " + (pagenum-1)*pagesize + "," + pagesize;   
    	return jdbcTemplate.query(sql, BeanPropertyRowMapper.newInstance(LockOpenLogEntity.class));
    }
    
    public int findOpenLogCountBySearch(String owner,String locksn,String keysn,String orgname,List<OrganizationEntity> orgs){
    	StringBuilder sb = new StringBuilder();
    	int i = 0;
    	for(OrganizationEntity org : orgs){
    		if(i == 0){
    			sb.append(org.getId() + "'");
    		}else{
    			sb.append(" or us.org_id = '" + org.getId()+ "'");
    		}
    		i++;
    	}
    	String sql = "select count(*) from tbl_lockopenlog olog left join tbl_users us on us.Username = olog.operator left join tbl_organization org on org.id = us.org_id where (us.org_id = '" + sb.toString();
    	
    	if(!owner.equals("")) {
    		sql += " and us.RealName like '%" + owner + "%'"; 
    	}
    	if(!locksn.equals("")) {
    		sql += " and olog.locksn like '%" + locksn + "'"; 
    	}
    	if(!keysn.equals("")) {
    		sql += " and olog.keysn like '%" + keysn + "'"; 
    	}
    	if(!orgname.equals("")) {
    		sql += " and org.Name like '%" + orgname + "%'"; 
    	} 
    	return jdbcTemplate.queryForObject(sql, Integer.class);
    }
    
    public List<LockOpenLogEntity> findOpenLogByOwner(int pagenum,int pagesize,String owner,String locksn,String keysn){
    	String sql = "select olog.*,org.Name as orgname,us.RealName as oper_reals from tbl_lockopenlog olog left join tbl_users us on us.Username = olog.operator left join tbl_organization org on org.id = us.org_id where us.Username = '" + owner + "'";

    	if(!locksn.equals("")) {
    		sql += " and olog.locksn like '%" + locksn + "'"; 
    	}
    	if(!keysn.equals("")) {
    		sql += " and olog.keysn like '%" + keysn + "'"; 
    	}

    	sql += " order by olog.locksn limit " + (pagenum-1)*pagesize + "," + pagesize;   
    	return jdbcTemplate.query(sql, BeanPropertyRowMapper.newInstance(LockOpenLogEntity.class));
    }
    
    public int findOpenLogCountByOwner(String owner,String locksn,String keysn){
    	String sql = "select count(*) from tbl_lockopenlog olog left join tbl_users us on us.Username = olog.operator left join tbl_organization org on org.id = us.org_id where us.Username = '" + owner + "'";

    	if(!locksn.equals("")) {
    		sql += " and olog.locksn like '%" + locksn + "'"; 
    	}
    	if(!keysn.equals("")) {
    		sql += " and olog.keysn like '%" + keysn + "'"; 
    	} 
    	return jdbcTemplate.queryForObject(sql, Integer.class);
    }
    
    /*====================================================================tbl_lock_user===============================================================================*/
    public int insertLockuser(LockUserEntity lockuser){    	
    	String sql = "INSERT INTO tbl_lock_user (id,missionid,locksn,operators,opentype,locktype,active,oper_reals)"
                + " VALUES (?, ?, ?, ?, ?, ?, ?, ?)";
    	try{
    		return jdbcTemplate.update(sql,lockuser.getId(),lockuser.getMissionid(),lockuser.getLocksn(),lockuser.getOperators(),lockuser.getOpentype(),lockuser.getLocktype(),lockuser.getActive(),lockuser.getOper_reals());
    	}catch(Exception e){
    		XinLongUtil.xinLongUtil.printlog(log,e.getMessage());
    		return 0;
    	}    	
    }
    
    public int updatelockuserActive(LockUserEntity lockuser){

		// update
		String sql = "UPDATE tbl_lock_user SET missionid=?,active=?,operators = ?,opentype = ?,locktype = ?,oper_reals=? WHERE locksn=?";
		try{
			jdbcTemplate.update(sql, lockuser.getMissionid(),lockuser.getActive(), lockuser.getOperators(),lockuser.getOpentype(),lockuser.getLocktype(),lockuser.getOper_reals(), lockuser.getLocksn());
		}catch(Exception e){
			XinLongUtil.xinLongUtil.printlog(log,e.getMessage());
			return 0;
		}
		return 1;
    	
    }
    
    public int setActiveByMission(int active,String missionid){

		// update
		String sql = "UPDATE tbl_lock_user SET active=? WHERE missionid=?";
		try{
			jdbcTemplate.update(sql, active, missionid);
		}catch(Exception e){
			XinLongUtil.xinLongUtil.printlog(log,e.getMessage());
			return -1;
		}
		return 1;
    	
    }
    
    public int setActiveByLocksn(int active,String locksn){

		// update
		String sql = "UPDATE tbl_lock_user SET active=? WHERE locksn=?";
		try{
			jdbcTemplate.update(sql, active, locksn);
		}catch(Exception e){
			XinLongUtil.xinLongUtil.printlog(log,e.getMessage());
			return -1;
		}
		return 1;
    	
    }
    
    public int ResetLockUserInfoByMission(String missionid){

		// update
		String sql = "UPDATE tbl_lock_user SET active=? WHERE missionid=?";
		try{
			jdbcTemplate.update(sql, 0, missionid);
		}catch(Exception e){
			XinLongUtil.xinLongUtil.printlog(log,e.getMessage());
			return -1;
		}
		return 1;
    	
    }
    
    public LockUserEntity findLockUserByLock(String locksn){    
    	String sql = "";     	
    	try {
    		sql = "SELECT * FROM tbl_lock_user where locksn = '" + locksn + "'";  
    		return jdbcTemplate.queryForObject(sql, BeanPropertyRowMapper.newInstance(LockUserEntity.class));
    	}catch(Exception e) {
    		return null;
    	}
    	
    }
    
    public List<LockUserEntity> findLockUserByMissionid(String missionid){    
    	String sql = "";     	
    	try {
    		sql = "SELECT * FROM tbl_lock_user where missionid = '" + missionid + "'";  
    		return jdbcTemplate.query(sql, BeanPropertyRowMapper.newInstance(LockUserEntity.class));
    	}catch(Exception e) {
    		return null;
    	}
    	
    }
    
    public List<LockEntity> findLocksByUser(String username){
    	String sql = "SELECT lk.* FROM tbl_lock_user luk left join tbl_lock lk on lk.locksn = luk.locksn where luk.operators like '%" + username + "%'";       
    	return jdbcTemplate.query(sql, BeanPropertyRowMapper.newInstance(LockEntity.class));
    }
    
    public List<LockUserEntity> findUserLocksByUser(String username){
    	String sql = "SELECT * FROM tbl_lock_user where operators like '%" + username + "%'";       
    	return jdbcTemplate.query(sql, BeanPropertyRowMapper.newInstance(LockUserEntity.class));
    }
    
    public List<LockUserEntity> findDoubleUserLocksByUser(String username){
    	String sql = "SELECT * FROM tbl_lock_user where opentype = 1 and operators like '%" + username + "%'";       
    	return jdbcTemplate.query(sql, BeanPropertyRowMapper.newInstance(LockUserEntity.class));
    }
    
    public int deleteuserBylock(String locksn) {
        String sql = "DELETE FROM tbl_lock_user WHERE locksn=?";
        try{
        	return jdbcTemplate.update(sql, locksn);
        }catch(Exception e){
        	XinLongUtil.xinLongUtil.printlog(log,e.getMessage());
        	return 0;
        }
        
    }
    
    public List<LockUserEntity> findAuthedUserLocksByUser(String username){
    	String sql = "SELECT * FROM tbl_lock_user where active = 1 and operators like '%" + username + "%'"; 
    	try {
    		return jdbcTemplate.query(sql, BeanPropertyRowMapper.newInstance(LockUserEntity.class));
    	}catch(Exception e) {
    		XinLongUtil.xinLongUtil.printlog(log,e.getMessage());
    		return null;
    	}
    	
    }
    
    /*====================================================================tbl_keys===============================================================================*/
    public int insertKey(KeyEntity keyentity){    	
    	String sql = "INSERT INTO tbl_keys (id,keysn,createname,owner,orgid,electricity,regdate,offname,offdate,lastreport,ckname,active,descriptor)"
                + " VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)";
    	try{
    		return jdbcTemplate.update(sql,keyentity.getId(),keyentity.getKeysn(),keyentity.getCreatename(),keyentity.getOwner(),keyentity.getOrgid(),keyentity.getElectricity(),
    				keyentity.getRegdate(),keyentity.getOffname(),keyentity.getOffdate(),keyentity.getLastreport(),keyentity.getCkname(),keyentity.getActive(),keyentity.getDescriptor());
    	}catch(DuplicateKeyException dx){
			XinLongUtil.xinLongUtil.printlog(log,dx.getMessage());
			return 2;
		}catch(Exception e){
    		XinLongUtil.xinLongUtil.printlog(log,e.getMessage());
    		return -1;
    	}    	
    }
    
    public int updateKey(KeyEntity keyentity){

		// update
		String sql = "UPDATE tbl_keys SET owner=?,active=?,orgid = ?,offname = ?,offdate = ?,lastreport = ?,ckname=?,descriptor=? WHERE keysn=?";
		try{
			jdbcTemplate.update(sql, keyentity.getOwner(),keyentity.getActive(), keyentity.getOrgid(),keyentity.getOffname(),keyentity.getOffdate(),
					keyentity.getLastreport(),keyentity.getCkname(), keyentity.getDescriptor(),keyentity.getKeysn());
		}catch(Exception e){
			XinLongUtil.xinLongUtil.printlog(log,e.getMessage());
			return -1;
		}
		return 1;
    	
    }
    
    public int updateKeyElectricity(String keysn, int electricity){

		// update
		String sql = "UPDATE tbl_keys SET electricity=?,lastreport=? WHERE keysn=?";
		try{
			jdbcTemplate.update(sql, electricity,new Date(),keysn);
		}catch(Exception e){
			XinLongUtil.xinLongUtil.printlog(log,e.getMessage());
			return -1;
		}
		return 1;
    	
    }
    
    public List<KeyEntity> findAllKeys(int pagenum,int pagesize,String orgname,String owner,String keysn,List<OrganizationEntity> orgs){
    	StringBuilder sb = new StringBuilder();
    	int i = 0;
    	for(OrganizationEntity org : orgs){
    		if(i == 0){
    			sb.append(org.getId() + "'");
    		}else{
    			sb.append(" or tkey.orgid = '" + org.getId()+ "'");
    		}
    		i++;
    	}
    	String sql = "SELECT tkey.*, org.Name as orgname,us.RealName as ownername FROM tbl_keys tkey left join tbl_organization org on org.id = tkey.orgid left join tbl_users us on us.Username = tkey.owner where tkey.active = 0 and (tkey.orgid = '" + sb.toString() + ")";
    	
    	if(!keysn.equals("")) {
    		sql += " and tkey.keysn like '%" + keysn + "'"; 
    	}
    	
    	if(!owner.equals("")) {
    		sql += " and us.RealName like '%" + owner + "%'";
    	}
    	
    	if(!orgname.equals("")) {
    		sql += " and org.Name like '%" + orgname + "%'"; 
    	}
    	
    	sql += " order by org.Orgcode,tkey.keysn limit " + (pagenum-1)*pagesize + "," + pagesize;   
    	return jdbcTemplate.query(sql, BeanPropertyRowMapper.newInstance(KeyEntity.class));
    }
    
    public int getKeysCount(String orgname,String owner,String keysn,List<OrganizationEntity> orgs){
    	StringBuilder sb = new StringBuilder();
    	int i = 0;
    	for(OrganizationEntity org : orgs){
    		if(i == 0){
    			sb.append(org.getId() + "'");
    		}else{
    			sb.append(" or tkey.orgid = '" + org.getId()+ "'");
    		}
    		i++;
    	}
    	String sql = "SELECT count(*) FROM tbl_keys tkey left join tbl_organization org on org.id = tkey.orgid left join tbl_users us on us.Username = tkey.owner where tkey.active = 0 and (tkey.orgid = '" + sb.toString() + ")";
    	
    	if(!keysn.equals("")) {
    		sql += " and tkey.keysn like '%" + keysn + "'"; 
    	}
    	
    	if(!owner.equals("")) {
    		sql += " and us.RealName like '%" + owner + "%'";
    	}
    	
    	if(!orgname.equals("")) {
    		sql += " and org.Name like '%" + orgname + "%'"; 
    	}
    	return jdbcTemplate.queryForObject(sql, Integer.class);
    }
    
    public List<KeyEntity> findAllKeys(int pagenum,int pagesize,String orgname,String owner,String keysn){
    	
    	String sql = "SELECT tkey.*, org.Name as orgname,us.RealName as ownername FROM tbl_keys tkey left join tbl_organization org on org.id = tkey.orgid left join tbl_users us on us.Username = tkey.owner where tkey.active = 0";
    	
    	if(!keysn.equals("")) {
    		sql += " and tkey.keysn like '%" + keysn + "'"; 
    	}
    	
    	if(!owner.equals("")) {
    		sql += " and us.RealName like '%" + owner + "%'";
    	}
    	
    	if(!orgname.equals("")) {
    		sql += " and org.Name like '%" + orgname + "%'"; 
    	}
    	
    	sql += " order by org.Orgcode,tkey.keysn limit " + (pagenum-1)*pagesize + "," + pagesize;   
    	return jdbcTemplate.query(sql, BeanPropertyRowMapper.newInstance(KeyEntity.class));
    }
    
    public int getKeysCount(String orgname,String owner,String keysn){
    	String sql = "SELECT count(*) FROM tbl_keys tkey left join tbl_organization org on org.id = tkey.orgid left join tbl_users us on us.Username = tkey.owner where tkey.active = 0";
    	
    	if(!keysn.equals("")) {
    		sql += " and tkey.keysn like '%" + keysn + "'"; 
    	}
    	
    	if(!owner.equals("")) {
    		sql += " and us.RealName like '%" + owner + "%'";
    	}
    	
    	if(!orgname.equals("")) {
    		sql += " and org.Name like '%" + orgname + "%'"; 
    	}
    	return jdbcTemplate.queryForObject(sql, Integer.class);
    }
    
    public List<KeyEntity> findKeysByUser(int pagenum,int pagesize,String keysn,String owner){
    	String sql = "SELECT tkey.*, org.Name as orgname,us.RealName as ownername FROM tbl_keys tkey left join tbl_organization org on org.id = tkey.orgid left join tbl_users us on us.Username = tkey.owner where tkey.active = 0 and tkey.owner = '" + owner + "'";

    	if(!keysn.equals("")) {
    		sql += " and tkey.keysn like '%" + keysn + "'"; 
    	}
    	sql += " order by tkey.keysn limit " + (pagenum-1)*pagesize + "," + pagesize;   
    	return jdbcTemplate.query(sql, BeanPropertyRowMapper.newInstance(KeyEntity.class));
    }
    
    public int findKeysByUserCount(String keysn,String owner){
    	String sql = "SELECT count(*) FROM tbl_keys where active = 0 and owner = '" + owner + "'";

    	if(!keysn.equals("")) {
    		sql += " and keysn like '%" + keysn + "'"; 
    	}
    	return jdbcTemplate.queryForObject(sql, Integer.class);
    }
    
    public KeyEntity findKeyBySn(String keysn){    
    	String sql = "";     	
    	try {
    		sql = "SELECT * FROM tbl_keys where keysn = '" + keysn + "'";  
    		return jdbcTemplate.queryForObject(sql, BeanPropertyRowMapper.newInstance(KeyEntity.class));
    	}catch(Exception e) {
    		return null;
    	}
    	
    }
    
    public int deletekey(String keysn) {
        String sql = "DELETE FROM tbl_keys WHERE keysn=?";
        try{
        	return jdbcTemplate.update(sql, keysn);
        }catch(Exception e){
        	XinLongUtil.xinLongUtil.printlog(log,e.getMessage());
        	return -1;
        }
        
    }
    
    public int offkey(String keysn,Date offdate,String offuser){

		// update
		String sql = "UPDATE tbl_keys SET active=?,offdate=?,offname=? WHERE keysn=?";
		try{
			jdbcTemplate.update(sql, 1,offdate,offuser, keysn);
		}catch(Exception e){
			XinLongUtil.xinLongUtil.printlog(log,e.getMessage());
			return -1;
		}
		return 1;
    	
    }
}
